import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.*;
import javax.servlet.http.*;

import javax.swing.JOptionPane;

public class editItemInDB extends HttpServlet
{	
	public void doPost(HttpServletRequest req, HttpServletResponse rsp)
	  throws ServletException, IOException 
	{
        String tablename = req.getParameter("table");
		   
		try 
		{
		    //initialises connection to database and associated tools
			Statement stmt;
		    Class.forName("com.mysql.jdbc.Driver");
		    String url = "jdbc:mysql://localhost:3306/ideas_i";
		    Connection con = DriverManager.getConnection(url, "root", "ideas_inventory");

		    //defines the stmt variable as a statement of the current connection
		    stmt = con.createStatement();
		    
		    //if the table is people (special case)
		    if (tablename.equals("software"))
		    {
		    	String softTitle = req.getParameter("form_item");
		    	String ownerName = req.getParameter("form_owner");
		    	String key = req.getParameter("form_cdkey");
		    	String dateAcq = req.getParameter("form_dateAcq");
		    	String dateRen = req.getParameter("form_dateRen");
		    	
		    	String executionString = "INSERT INTO "+ tablename + " VALUES (\"" +
		    	                         softTitle + "\", \"" + ownerName + "\", \"" +
		    	                         key + "\", \"" + dateAcq + "\", \"" + dateRen +
		    	                         "\");";
		    	
		    	stmt.executeUpdate(executionString);
		    }
		    else if (tablename.equals("books"))
		    {
		    	String bookTitle = req.getParameter("form_item");
		    	String ownerName = req.getParameter("form_owner");
		    	
		    	String executionString = "INSERT INTO " + tablename + " VALUES (\"" +
		    	                         bookTitle + "\", \"" + ownerName + "\");";
		    	                         
		    	stmt.executeUpdate(executionString);
		    }

		    else if (tablename.equals("people"))
		    {
		    	//get parameters sent by the form
		    	String name = req.getParameter("form_name");
		    	String office = req.getParameter("form_office");
		    	String email = req.getParameter("form_email");
		    	String involvement = req.getParameter("form_invprd");
		    	String tasks = req.getParameter("form_tasks");
		    	String workst = req.getParameter("form_workst");
		    	
		    	
		    	//construct the SQL statement string to append the parameter values
		    	String executionString = "INSERT INTO "+ tablename + " VALUES (\"" + 
		    	                         name + "\", \"" + office + "\", \"" + email + 
		    	                         "\", \"" + involvement + "\", \"" + tasks +
		    	                         "\", \"" + workst + "\");";
		    	
		    	//execute the statement string
		    	stmt.executeUpdate(executionString);  
		    }
		}
		//error handling currently not working
		catch (Exception e)
		{
			JOptionPane.showMessageDialog(null, "You have triggered an input error:" + e);
		}
	}
	public void doGet (HttpServletRequest req, HttpServletResponse rsp)
	  throws ServletException, IOException
	{
		//gets the initial parameter and initialises the response
		String tablename = req.getParameter("table");
		rsp.setContentType("text/html");
		PrintWriter out = rsp.getWriter();
		try 
	    {
			//initialises connection to database and associated tools
	        Statement stmt;
	        Class.forName("com.mysql.jdbc.Driver");
	        String url = "jdbc:mysql://localhost:3306/ideas_i";
	        Connection con = DriverManager.getConnection(url, "root", "ideas_inventory");

	        //defines the stmt variable as a statement of the current connection
	        stmt = con.createStatement();
	        //selects an initial Resultset used by all tables based on the 
	        //given table parameter
	        ResultSet result = stmt.executeQuery("SELECT * FROM " + tablename + ";");
	        
	        
	        if (tablename.equals("software"))
	        {
	        	out.println("<th class=\"records\">Software Title</th>");
	        	out.println("<th class=\"records\">Current Owner</th>");
	        	out.println("<th class=\"records\">License Key</th>");
	        	out.println("<th class=\"records\">Date Acquired</th>");
	        	out.println("<th class=\"records\">Renewal Date</th>");
	        	
	        	while(result.next())
	        	{
	        		out.println("<tr>");
	        		out.println("<td class=\"records\">" + result.getString(1) + "</td>");
	        		out.println("<td class=\"records\">" + result.getString(2) + "</td>");
	        		out.println("<td class=\"records\">" + result.getString(3) + "</td>");
	        		out.println("<td class=\"records\">" + result.getString(4) + "</td>");
	        		out.println("<td class=\"records\">" + result.getString(5) + "</td>");
	        		out.println("</tr>");
	        	}
	        }
	        else if (tablename.equals("books"))
	        {
	        	out.println("<th class=\"records\">Book Title</th>");
	        	out.println("<th class=\"records\">Current Owner</th>");
	        	
	        	while(result.next())
	        	{
	        		out.println("<tr>");
	        		out.println("<td class=\"records\">" + result.getString(1) + "</td>");
	        		out.println("<td class=\"records\">" + result.getString(2) + "</td>");
	        		out.println("</tr>");
	        	}
	        }
	        
	        //if the table is people (special case)
	        else if (tablename.equals("people"))
	        {
	        	
	        	//print the table headers
		        out.println("<th class=\"records\">Name</th>");
		       	out.println("<th class=\"records\">Office Location</th>");
		       	out.println("<th class=\"records\">Email Address</th>");
		       	out.println("<th class=\"records\">Period of Involvement</th>");
		       	out.println("<th class=\"records\">Tasks</th>");
		       	out.println("<th class=\"records\">Workstation</th>");
		        	
		       	//loop through the resultset to construct the contents of the table
		       	while(result.next())
		       	{
		       		out.println("<tr>");
		       		out.println("<td class=\"records\">" + result.getString(1) + "</td>");
		       		out.println("<td class=\"records\">" + result.getString(2) + "</td>");
		       		out.println("<td class=\"records\">" + result.getString(3) + "</td>");
		       		out.println("<td class=\"records\">" + result.getString(4) + "</td>");
		       		out.println("<td class=\"records\">" + result.getString(5) + "</td>");
		       		out.println("<td class=\"records\">" + result.getString(6) + "</td>");
		       		out.println("</tr>");
		       	} 	
	        }
	    }
		//prints an appropriate exception error message in place of the table
	    catch (Exception e)
	    {
	    	out.println("<tr><td>Error in servlet: " + e + "</td></tr>");
	    }
	}
	
	protected void printQueryRow(ResultSet set,PrintWriter printer, int numCols, String table)
	{
    	try 
    	{
    		printer.println("<tr>");
    		printer.println("<td class=\"records\">" + table + "</td>");
    		for (int i = 1; i < numCols + 1; i++)
    		{
    			printer.println("<td class=\"records\">" + set.getString(i) + "</td>");
    		}
    		printer.println("</tr>");
			
		} 
    	catch (SQLException e) 
		{
			printer.println("<tr><td>Error in servlet: " + e + "</td></tr>");
		}
	}
}
